﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.IO;
using System.Globalization;

namespace Possan.MySQL.SqlServerCodeGenerator
{
	class Generator
	{
		public string ConnectionString;

		public string Table;
		public string OutputTablename;

		public string OutputFile;
		public StreamWriter OutputWriter;

		public int Limit;

		public bool IncludeDeletes;
		public bool Overwrite;

		public List<string> ExcludeFields;
		public List<string> IncludeFields;
		public string PrimaryKey;

		public bool LowercaseNames;

		public Generator()
		{
			Table = "";
			OutputTablename = "";
			OutputFile = "";
			ConnectionString = "";
			Limit = 0;
			ExcludeFields = new List<string>();
			IncludeFields = new List<string>();
			PrimaryKey = "";
			LowercaseNames = false;
		}

		void Debug(string msg)
		{
			Console.WriteLine("-- " + msg);
		}

		void DumpFile(string msg)
		{
			// 
			if (OutputWriter != null)
				OutputWriter.WriteLine(msg);
			else
				Console.WriteLine(msg);
		}

		public void Run()
		{
			OutputTablename = Table;
			if (LowercaseNames)
				OutputTablename = Table.ToLower();

			// connect
			Debug("Connecting.");
			using (SqlConnection sc = new SqlConnection(ConnectionString))
			{
				sc.Open();

				OutputWriter = null;
				if (!string.IsNullOrEmpty(OutputFile))
				{
					if (File.Exists(OutputFile) && !Overwrite)
						OutputWriter = File.AppendText(OutputFile);
					else
						OutputWriter = File.CreateText(OutputFile);
				}


				// query
				Debug("Finding columns.");
				List<ColumnInfo> columns = new List<ColumnInfo>();
				using (SqlCommand cmd = sc.CreateCommand())
				{
					cmd.CommandText = @"SELECT * FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
						WHERE TABLE_NAME = @tn ORDER BY ORDINAL_POSITION";
					cmd.Parameters.Add(new SqlParameter("tn", Table));

					using (SqlDataReader dr = cmd.ExecuteReader())
					{
						int index = 0;
						while (dr.Read())
						{
							ColumnInfo ci = new ColumnInfo();
							ci.Index = index;
							ci.Name = dr["COLUMN_NAME"].ToString();
							ci.Datatype = dr["DATA_TYPE"].ToString();

							ci.IncludedInResult = (IncludeFields.Count > 0) ?
								(IncludeFields.IndexOf(ci.Name) != -1) :
								(ExcludeFields.IndexOf(ci.Name) == -1);

							//if (IncludeFields.Count > 0)
							//	ci.IncludedInResult = (IncludedFields.IndexOf(ci.Name) != -1);
							columns.Add(ci);

							Debug(string.Format("Got column #{0}, name={1}, datatype={2}, included={3}, pk={4}", ci.Index, ci.Name, ci.Datatype, ci.IncludedInResult, ci.IsPK));
							index++;
						}
						dr.Close();
					}
				}

				int totalrows = 0;

				using (SqlCommand cmd = sc.CreateCommand())
				{
					cmd.CommandText = "select count(*) as n from " + Table;
					using (DbDataReader dr = cmd.ExecuteReader())
					{
						if (dr.Read())
						{
							int.TryParse(dr["n"].ToString(), out totalrows);
						}
						dr.Close();
					}
				}

				Debug("Querying table " + Table + " (" + totalrows + " rows, exporting as "+OutputTablename+")...");

				using (SqlCommand cmd = sc.CreateCommand())
				{
					string insertcols = "";
					string what = "";

					foreach (ColumnInfo ci in columns)
					{
						if (ci.IncludedInResult)
						{
							if (what != "")
								what += ", ";

								what += ci.Name;

							if (insertcols != "")
								insertcols += ",";

							if (LowercaseNames)
								insertcols += "`" + ci.Name.ToLower() + "`";
							else
								insertcols += "`" + ci.Name + "`"; // translate name here
						}
					}

					if (IncludeDeletes)
						DumpFile("delete from " + Table + ";\n\n");

					string top = "";
					if (Limit > 0)
						top = " top " + Limit + " ";
					cmd.CommandText = "select " + top + what + " from " + Table;

					// while
					//	Debug("Querying table " + Table + " ...");
					using (DbDataReader dr = cmd.ExecuteReader())
					{
						int rowcounter = 0;
						while (dr.Read())
						{
							if (rowcounter % 500 == 0)
								Debug("Exporting row " + (rowcounter + 1) + "/" + totalrows + "...");

							rowcounter++;

							string insertvals = "";
							foreach (ColumnInfo ci in columns)
							{
								if (ci.IncludedInResult)
								{
									if (insertvals != "")
										insertvals += ",";

									string escapedval = "";

									object value = dr[ci.Name];

									if (value is DBNull)
									{
										escapedval = "NULL";
									}
									else if (ci.Datatype == "bit")
									{
										if ((bool)value == true)
											escapedval = "1";
										else
											escapedval = "0";
									}
									else if (ci.Datatype == "float")
									{
										float f = .0f;
										float.TryParse(value.ToString(), out f);
										escapedval = f.ToString(CultureInfo.InvariantCulture);
									}
									else if (ci.Datatype == "double")
									{
										double d = .0;
										double.TryParse(value.ToString(), out d);
										escapedval = d.ToString(CultureInfo.InvariantCulture);
									}
									else if (ci.Datatype == "datetime")
									{
										DateTime dt = (DateTime)value;

										escapedval = "'" + dt.ToString(CultureInfo.GetCultureInfo("sv-SE")) + "'";
									}
									else
									{
										string strvalue = value.ToString();

										strvalue = strvalue.Replace("\\", "\\\\");
										strvalue = strvalue.Replace("\'", "\\\'");
										strvalue = strvalue.Replace("\"", "\\\"");
										strvalue = strvalue.Replace("\r", "\\r");
										strvalue = strvalue.Replace("\n", "\\n");
										strvalue = strvalue.Replace("\t", "\\t");

										escapedval = "'" + strvalue + "'";
									}

									insertvals += escapedval;
								}
							}

							DumpFile("insert into " + OutputTablename + " ( " + insertcols + " ) values ( " + insertvals + " );\n");
						}
						dr.Close();
					}
				}

				if (OutputWriter != null)
					OutputWriter.Close();

				// disconnect
				Debug("Disconnecting.");
				sc.Close();
			}
		}

		class ColumnInfo
		{
			public bool IsPK;
			public bool IncludedInResult;
			public int Index;
			public string Name;
			public string Datatype;
		}
	}
}
